const pool = require('../config/db');

/**
 * 获取所有轮播图列表
 * @param {boolean} includeImageData - 是否包含图片二进制数据
 * @returns {Promise<Array>} 轮播图列表
 */
const getCarousels = async (includeImageData = false) => {
    let query;
    if (includeImageData) {
        // 包含图片数据
        query = 'SELECT * FROM carousels ORDER BY sort_order ASC';
    } else {
        // 不包含图片数据，减少传输量
        query = `
            SELECT id, image_url, image_blob, store_id, title, sort_order, 
                   mime_type, file_name, file_size, created_at, updated_at 
            FROM carousels 
            ORDER BY sort_order ASC
        `;
    }
    const [rows] = await pool.execute(query);
    return rows;
};

/**
 * 获取单个轮播图，包含图片数据
 * @param {number} id - 轮播图ID
 * @returns {Promise<Object>} 轮播图对象
 */
const getCarouselById = async (id) => {
    const [rows] = await pool.execute('SELECT * FROM carousels WHERE id = ?', [id]);
    return rows[0] || null;
};

/**
 * 获取轮播图图片数据
 * @param {number} id - 轮播图ID
 * @returns {Promise<Object>} 图片数据对象
 */
const getCarouselImage = async (id) => {
    const [rows] = await pool.execute(
        'SELECT image_blob, mime_type, file_name FROM carousels WHERE id = ?', 
        [id]
    );
    return rows[0] || null;
};

/**
 * 创建新轮播图（带BLOB图片）
 * @param {Object} carousel - 轮播图对象
 * @returns {Promise<Object>} 创建结果
 */
const createCarousel = async (carousel) => {
    const [result] = await pool.execute(
        `INSERT INTO carousels 
        (image_url, store_id, title, image_data, mime_type, file_name, file_size, sort_order) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
        [
            carousel.image_url || null, 
            carousel.store_id || null, 
            carousel.title || null,
            carousel.image_data || null,
            carousel.mime_type || null,
            carousel.file_name || null,
            carousel.file_size || null,
            carousel.sort_order || 0
        ]
    );
    return result;
};

/**
 * 更新轮播图
 * @param {number} id - 轮播图ID
 * @param {Object} carousel - 轮播图更新数据
 * @returns {Promise<void>}
 */
const updateCarousel = async (id, carousel) => {
    // 构建动态更新SQL
    let sql = 'UPDATE carousels SET ';
    const params = [];
    const fields = [];

    if (carousel.image_url !== undefined) {
        fields.push('image_url = ?');
        params.push(carousel.image_url);
    }
    
    if (carousel.store_id !== undefined) {
        fields.push('store_id = ?');
        params.push(carousel.store_id);
    }
    
    if (carousel.title !== undefined) {
        fields.push('title = ?');
        params.push(carousel.title);
    }
    
    if (carousel.sort_order !== undefined) {
        fields.push('sort_order = ?');
        params.push(carousel.sort_order);
    }

    // 图片相关字段
    if (carousel.image_data !== undefined) {
        fields.push('image_data = ?');
        params.push(carousel.image_data);
    }
    
    if (carousel.mime_type !== undefined) {
        fields.push('mime_type = ?');
        params.push(carousel.mime_type);
    }
    
    if (carousel.file_name !== undefined) {
        fields.push('file_name = ?');
        params.push(carousel.file_name);
    }
    
    if (carousel.file_size !== undefined) {
        fields.push('file_size = ?');
        params.push(carousel.file_size);
    }

    if (fields.length === 0) {
        return; // 没有需要更新的字段
    }

    sql += fields.join(', ');
    sql += ' WHERE id = ?';
    params.push(id);

    await pool.execute(sql, params);
};

/**
 * 删除轮播图
 * @param {number} id - 轮播图ID
 * @returns {Promise<void>}
 */
const deleteCarousel = async (id) => {
    await pool.execute('DELETE FROM carousels WHERE id = ?', [id]);
};

module.exports = { 
    getCarousels, 
    getCarouselById,
    getCarouselImage,
    createCarousel, 
    updateCarousel, 
    deleteCarousel 
};